package dataGateway;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import domainLogic.Category;


/**
 * @author  jungmins
 */
public class CategoryTable {

	/**
	 * @uml.property  name="instance"
	 * @uml.associationEnd  
	 */
	private static CategoryTable instance = new CategoryTable();
	
	/**
	 * @return
	 * @uml.property  name="instance"
	 */
	public static CategoryTable getInstance()
	{
		return instance;
	}
		
	public CategoryTable() {  }
	
	private Connection getConnection() throws Exception {
		return ConnectionPool.getInstance().getConnection();
	} //getConnection()
	
	public int insertCategory(Category item) {
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		PreparedStatement pstmt = null;
		int id = -1;
		
		try {
			conn = getConnection();
			String sql = "insert into categories(name) values(?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,item.getName()); 
			pstmt.executeUpdate();
			
			ResultSet res = pstmt.getGeneratedKeys();
			if(res != null && res.next())
				id = res.getInt(1);
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if(pstmt != null) try { pstmt.close(); } catch(SQLException ex) { }		
			if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
		}
		System.out.println("insertCategory: "+(System.currentTimeMillis()-startTime));
		
		return id;
	} //insertMember
	
	public int updateCategory(Category item) {
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		PreparedStatement pstmt = null;
		int id = -1;
		
		try {
			conn = getConnection();
			String sql = "update categories set name = ? where id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,item.getName()); 
			pstmt.setInt(2,item.getId()); 
			pstmt.executeUpdate();
			
			ResultSet res = pstmt.getGeneratedKeys();
			if(res != null && res.next())
				id = res.getInt(1);
		} catch(Exception ex) {
			ex.printStackTrace();
		} finally {
			if(pstmt != null) try { pstmt.close(); } catch(SQLException ex) { }		
			if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
		}
		System.out.println("insertCategory: "+(System.currentTimeMillis()-startTime));
		
		return id;
	} //insertMember

	public List<Category> getCategoriesAll()
	{
		long startTime = System.currentTimeMillis();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		ArrayList<Category> ret = new ArrayList<Category>();
		
		try {
			conn = getConnection();
			String sql = "select * from categories ";
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) 
			{
				ret.add(new Category(rs));
			} 
		} catch(Exception ex) {
					ex.printStackTrace();
		} finally {
			if(rs    != null) try {    rs.close(); } catch(SQLException ex) { }		
			if(stmt != null) try { stmt.close(); } catch(SQLException ex) { }		
			if(conn  != null) try {  conn.close(); } catch(SQLException ex) { }
		}
		System.out.println("getCategoriesAll: "+(System.currentTimeMillis()-startTime));
		return ret;
	}
} //class
